-- @owner: ningyali
-- @date: 2025-1-17
-- @testpoint: HTAP行列融合特性--转换前后创建函数引用表数据，数据更新验证

--step1: 创建表A并插入数据 expect: 执行成功
@conn SingleDbUser;
drop function if exists func_htap_single_0153;
drop table if exists t_htap_single_0153 cascade;
create table t_htap_single_0153(c1 int, c2 varchar(20), c3 timestamp);
insert into t_htap_single_0153 values (
generate_series(1,10000), 'aaa'||generate_series(1,10000), now());

--step2: 创建函数引用表数据 expect: 执行成功
create or replace function func_htap_single_0153()
returns table(c1 int, c2 varchar(20))
language plpgsql
as $$
begin
  return query select c1, c2 from t_htap_single_0153 
where c1 between 5990 and 6000 order by c1;
end;
$$;
/

--step3: 表A部分列进行行列转换 expect: 执行成功
alter table t_htap_single_0153 imcstored(c1);

--step4: 查询 expect: 执行成功
set enable_imcsscan to on;
explain (costs false) select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;
select func_htap_single_0153();
select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;

--step5: 主机执行DML操作 expect: 执行成功
insert into t_htap_single_0153 values (
generate_series(10001,20000),'bbb'||generate_series(10001,20000), now());
delete from t_htap_single_0153 where c1<3000;
update t_htap_single_0153 set c2='ccc' where c1%2=0;

--step6: 查询 expect: 执行成功
set enable_imcsscan to on;
explain (costs false) select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;
select func_htap_single_0153();
select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;

--step7: 取消转换 expect: 执行成功
alter table t_htap_single_0153 unimcstored;

--step8: 全表转换 expect: 执行成功
alter table t_htap_single_0153 imcstored;

--step9: 查询 expect: 执行成功
explain (costs false) select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;
select func_htap_single_0153();
select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;

--step10: 主机执行DML操作 expect: 执行成功
insert into t_htap_single_0153 values (
generate_series(20001,30000),'ddd'||generate_series(10001,20000), now());
delete from t_htap_single_0153 where c1>8000;
update t_htap_single_0153 set c2='eee' where c1%3=0;

--step11: 查询 expect: 执行成功
explain (costs false) select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;
select func_htap_single_0153();
select count(c1) from t_htap_single_0153 where c1>=88 and c1<=95869;

--step12: 清理环境 expect: 执行成功
drop function if exists func_htap_single_0153;
drop table if exists t_htap_single_0153 cascade;